//    Copyright (C) 2013  <lugocreation@gmail.com>
//
//    This program is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//    This program is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with this program.  If not, see <http://www.gnu.org/licenses/>
package org.jpgolay.wi.persistence;

public class PersistenceSQL {

    public PersistenceSQL() {
    }
    public static final String AGENCY_INSERT = "INSERT INTO AGENCY (ID,LAST_UPDATE) VALUES (?,?)";
    public static final String AGENCY_UPDATE = "UPDATE AGENCY SET ID=?,GROUP_ID=?,USERNAME=?,PASS=?,COMPAGNY=?,STATUS=?,ADDR_LN1=?,ADDR_LN2=?,CITY=?,COUNTY=?,ZIP=?,TEL1=?,TEL2=?,FAX=?,EMAIL=?,WEB=?,ACCESS=?,WWW=?,WELCOME_EMAIL=?,WELCOME_TITLE=?,NEW_AD_EMAIL_UP=?,NEW_AD_EMAIL_DOWN=?,NEW_AD_TITLE=?,SMTP=?,EXPIRY_DATE=?,PAYMENT_DUE=?,COMMENT=?,LOGO=?,LAST_UPDATE=?,LAST_USER=?,SIRET=?,NAF=? WHERE ID=? AND LAST_UPDATE=?";
    public static final String AGENCY_DELETE = "DELETE FROM AGENCY WHERE ID=? AND LAST_UPDATE=?";
    public static final String AGENCY_CASCADE_DELETE_USER = "DELETE FROM USER WHERE AGENCY_ID=?";
    public static final String AGENCY_CASCADE_DELETE_REALESTATE = "DELETE FROM REALESTATE WHERE AGENCY_ID=?";
    public static final String AGENCY_SELECT = "SELECT ID,GROUP_ID,USERNAME,PASS,COMPAGNY,STATUS,ADDR_LN1,ADDR_LN2,CITY,COUNTY,ZIP,TEL1,TEL2,FAX,EMAIL,WEB,ACCESS,WWW,WELCOME_EMAIL,WELCOME_TITLE,NEW_AD_EMAIL_UP,NEW_AD_EMAIL_DOWN,NEW_AD_TITLE,SMTP,EXPIRY_DATE,PAYMENT_DUE,COMMENT,LOGO,LAST_UPDATE,LAST_USER,SIRET,NAF FROM AGENCY WHERE ID=?";
    public static final String AGENCY_SELECT_BY = "SELECT ID,GROUP_ID,USERNAME,PASS,COMPAGNY,STATUS,ADDR_LN1,ADDR_LN2,CITY,COUNTY,ZIP,TEL1,TEL2,FAX,EMAIL,WEB,ACCESS,WWW,WELCOME_EMAIL,WELCOME_TITLE,NEW_AD_EMAIL_UP,NEW_AD_EMAIL_DOWN,NEW_AD_TITLE,SMTP,EXPIRY_DATE,PAYMENT_DUE,COMMENT,LOGO,LAST_UPDATE,LAST_USER,SIRET,NAF FROM AGENCY WHERE ID=? AND GROUP_ID=?";
    public static final String AGENCY_SELECT_ALL = "SELECT ID,GROUP_ID,USERNAME,PASS,COMPAGNY,STATUS,ADDR_LN1,ADDR_LN2,CITY,COUNTY,ZIP,TEL1,TEL2,FAX,EMAIL,WEB,ACCESS,WWW,WELCOME_EMAIL,WELCOME_TITLE,NEW_AD_EMAIL_UP,NEW_AD_EMAIL_DOWN,NEW_AD_TITLE,SMTP,EXPIRY_DATE,PAYMENT_DUE,COMMENT,LOGO,LAST_UPDATE,LAST_USER,SIRET,NAF FROM AGENCY";
    public static final String AGENCY_NEW_ID = "SELECT MAX(ID)+1 FROM AGENCY";
    public static final String GROUP_INSERT = "INSERT INTO GROUPS (ID,LAST_UPDATE) VALUES (?,?)";
    public static final String GROUP_UPDATE = "UPDATE GROUPS SET ID=?,NAME=?,ROLE=?,NOTE=?,LAST_UPDATE=?,LAST_USER=? WHERE ID=? AND LAST_UPDATE=?";
    public static final String GROUP_DELETE = "DELETE FROM GROUPS WHERE ID=? AND LAST_UPDATE=?";
    public static final String GROUP_CASCADE_DELETE_USER = "DELETE FROM USER WHERE GROUP_ID=?";
    public static final String GROUP_CASCADE_SELECT_AGENCY = "SELECT ID FROM AGENCY WHERE GROUP_ID=?";
    public static final String GROUP_CASCADE_DELETE_AGENCY = "DELETE FROM AGENCY WHERE GROUP_ID=?";
    public static final String GROUP_SELECT = "SELECT ID,NAME,ROLE,NOTE,LAST_UPDATE,LAST_USER FROM GROUPS WHERE ID=?";
    public static final String GROUP_SELECT_BY = "SELECT ID,NAME,ROLE,NOTE,LAST_UPDATE,LAST_USER FROM GROUPS WHERE ID=? AND ROLE=?";
    public static final String GROUP_SELECT_ALL = "SELECT ID,NAME,ROLE,NOTE,LAST_UPDATE,LAST_USER FROM GROUPS";
    public static final String GROUP_NEW_ID = "SELECT MAX(ID)+1 FROM GROUPS";
    public static final String USER_INSERT = "INSERT INTO USER (ID,LAST_UPDATE) VALUES (?,?)";
    public static final String USER_UPDATE = "UPDATE USER SET ID=?,GROUP_ID=?,USERNAME=?,PASS=?,LASTNAME=?,FIRSTNAME=?,ADDR_LN1=?,ADDR_LN2=?,CITY=?,COUNTY=?,ZIP=?,TEL1=?,TEL2=?,FAX=?,EMAIL=?,WEB=?,AGENCY_ID=?,ACCESS=?,EXPIRY_DATE=?,PAYMENT_DUE=?,COMMENT=?,LOGO=?,LAST_UPDATE=?,LAST_USER=? WHERE ID=? AND LAST_UPDATE=?";
    public static final String USER_DELETE = "DELETE FROM USER WHERE ID=? AND LAST_UPDATE=?";
    public static final String USER_SELECT = "SELECT ID,GROUP_ID,USERNAME,PASS,LASTNAME,FIRSTNAME,ADDR_LN1,ADDR_LN2,CITY,COUNTY,ZIP,TEL1,TEL2,FAX,EMAIL,WEB,AGENCY_ID,ACCESS,EXPIRY_DATE,PAYMENT_DUE,COMMENT,LOGO,LAST_UPDATE,LAST_USER FROM USER WHERE ID=?";
    public static final String USER_SELECT_BY = "SELECT ID,GROUP_ID,USERNAME,PASS,LASTNAME,FIRSTNAME,ADDR_LN1,ADDR_LN2,CITY,COUNTY,ZIP,TEL1,TEL2,FAX,EMAIL,WEB,AGENCY_ID,ACCESS,EXPIRY_DATE,PAYMENT_DUE,COMMENT,LOGO,LAST_UPDATE,LAST_USER FROM USER WHERE USERNAME=? AND PASS=?";
    public static final String USER_SELECT_ALL = "SELECT ID,GROUP_ID,USERNAME,PASS,LASTNAME,FIRSTNAME,ADDR_LN1,ADDR_LN2,CITY,COUNTY,ZIP,TEL1,TEL2,FAX,EMAIL,WEB,AGENCY_ID,ACCESS,EXPIRY_DATE,PAYMENT_DUE,COMMENT,LOGO,LAST_UPDATE,LAST_USER FROM USER WHERE GROUP_ID=?";
    public static final String USER_NEW_ID = "SELECT MAX(ID)+1 FROM USER";
    public static final String REALESTATE_INSERT = "INSERT INTO REALESTATE (ID,LAST_UPDATE) VALUES (?,?)";
    public static final String REALESTATE_UPDATE = "UPDATE REALESTATE SET ADDR_LN1=?,ADDR_LN2=?,AGENCY_FEES=?,ARCHIVE=?,AREA=?,ASPECT=?,AVAILABILITY=?,BALCONY=?,BATCH=?,BATHROOM=?,BED=?,BEDROOM=?,CATEGORY=?,CELLAR=?,CHARGE=?,CHARGE_DEPOSIT=?,CONDITIONS=?,COUNTY=?,COUNTRY=?,CREATEDATE=?,CUPBOARD=?,DEPARTURE=?,DEPARTURE_CUSTOMER=?,DEPOSIT=?,DESCRIPTION=?,ELEVATOR=?,FIREPLACE=?,FLOOR=?,FURNISHED=?,GARAGE=?,GARDEN=?,HALL=?,HEATING=?,ID=?,ID_CUSTOMER=?,BUNCHKEY=?,KITCHEN=?,LAST_UPDATE=?,LAST_USER=?,LIFEANNUITY=?,LIVINGROOM=?,LOGGIA=?,MEZZANINE=?,NUM_CONSULT_CC=?,NUM_CONSULT_WEB=?,OBSERVATION=?,ONLYOPTION=?,OPTION_FISC=?,PANEL=?,PARKING=?,PHOTO1=?,PHOTO2=?,PHOTO3=?,PHOTO4=?,PHOTO5=?,PHOTO6=?,PHOTO7=?,PHOTO8=?,PHOTO9=?,PHOTO10=?,PHOTOS_VALID=?,POOL=?,PLOT=?,PRINT_OBSERVATION=?,REF=?,REF_INTERNAL=?,RENT=?,RESERVE=?,RESTROOM=?,ROOFGARDEN=?,SALE=?,SECTOR=?,SHOWER=?,SORT=?,STANDING=?,STATE=?,STYLEFLOOR=?,SURFACE_FLOOR=?,SURFACE_GROUND=?,TV=?,TYPE=?,VACATION=?,WEB=?,ZIP=?,AGENCY_ID=?,EXIT_CUSTOMER=?,S_GARAGE=?,S_LIVINGROOM=?,S_HALL=?,S_ROOFGARDEN=?,S_MEZZANINE=?,S_LOGGIA=?,S_ROOM=?,S_CELLAR=?,CUSTOMER=?,RESIDANCE=?,S_KITCHEN=?,S_BALCONY=?,CELLAR_WINE=?,WALLS=?,DUPLEX=?,S_CELLAR_WINE=? WHERE ID=? AND LAST_UPDATE=?";
    public static final String REALESTATE_DELETE = "DELETE FROM REALESTATE WHERE ID=? AND LAST_UPDATE=?";
    public static final String REALESTATE_SELECT = "SELECT ADDR_LN1,ADDR_LN2,AGENCY_FEES,ARCHIVE,AREA,ASPECT,AVAILABILITY,BALCONY,BATCH,BATHROOM,BED,BEDROOM,CATEGORY,CELLAR,CHARGE,CHARGE_DEPOSIT,CONDITIONS,COUNTY,COUNTRY,CREATEDATE,CUPBOARD,DEPARTURE,DEPARTURE_CUSTOMER,DEPOSIT,DESCRIPTION,ELEVATOR,FIREPLACE,FLOOR,FURNISHED,GARAGE,GARDEN,HALL,HEATING,ID,ID_CUSTOMER,BUNCHKEY,KITCHEN,LAST_UPDATE,LAST_USER,LIFEANNUITY,LIVINGROOM,LOGGIA,MEZZANINE,NUM_CONSULT_CC,NUM_CONSULT_WEB,OBSERVATION,ONLYOPTION,OPTION_FISC,PANEL,PARKING,PHOTO1,PHOTO2,PHOTO3,PHOTO4,PHOTO5,PHOTO6,PHOTO7,PHOTO8,PHOTO9,PHOTO10,PHOTOS_VALID,POOL,PLOT,PRINT_OBSERVATION,REF,REF_INTERNAL,RENT,RESERVE,RESTROOM,ROOFGARDEN,SALE,SECTOR,SHOWER,SORT,STANDING,STATE,STYLEFLOOR,SURFACE_FLOOR,SURFACE_GROUND,TV,TYPE,VACATION,WEB,ZIP,AGENCY_ID,EXIT_CUSTOMER,S_GARAGE,S_LIVINGROOM,S_HALL,S_ROOFGARDEN,S_MEZZANINE,S_LOGGIA,S_ROOM,S_CELLAR,CUSTOMER,RESIDANCE,S_KITCHEN,S_BALCONY,CELLAR_WINE,WALLS,DUPLEX,S_CELLAR_WINE FROM REALESTATE WHERE ID=?";
    public static final String REALESTATE_SELECT_BY = "SELECT ADDR_LN1,ADDR_LN2,AGENCY_FEES,ARCHIVE,AREA,ASPECT,AVAILABILITY,BALCONY,BATCH,BATHROOM,BED,BEDROOM,CATEGORY,CELLAR,CHARGE,CHARGE_DEPOSIT,CONDITIONS,COUNTY,COUNTRY,CREATEDATE,CUPBOARD,DEPARTURE,DEPARTURE_CUSTOMER,DEPOSIT,DESCRIPTION,ELEVATOR,FIREPLACE,FLOOR,FURNISHED,GARAGE,GARDEN,HALL,HEATING,ID,ID_CUSTOMER,BUNCHKEY,KITCHEN,LAST_UPDATE,LAST_USER,LIFEANNUITY,LIVINGROOM,LOGGIA,MEZZANINE,NUM_CONSULT_CC,NUM_CONSULT_WEB,OBSERVATION,ONLYOPTION,OPTION_FISC,PANEL,PARKING,PHOTO1,PHOTO2,PHOTO3,PHOTO4,PHOTO5,PHOTO6,PHOTO7,PHOTO8,PHOTO9,PHOTO10,PHOTOS_VALID,POOL,PLOT,PRINT_OBSERVATION,REF,REF_INTERNAL,RENT,RESERVE,RESTROOM,ROOFGARDEN,SALE,SECTOR,SHOWER,SORT,STANDING,STATE,STYLEFLOOR,SURFACE_FLOOR,SURFACE_GROUND,TV,TYPE,VACATION,WEB,ZIP,AGENCY_ID,EXIT_CUSTOMER,S_GARAGE,S_LIVINGROOM,S_HALL,S_ROOFGARDEN,S_MEZZANINE,S_LOGGIA,S_ROOM,S_CELLAR,CUSTOMER,RESIDANCE,S_KITCHEN,S_BALCONY,CELLAR_WINE,WALLS,DUPLEX,S_CELLAR_WINE FROM REALESTATE WHERE REF_INTERNAL=? AND AGENCY_ID=?";
    public static final String REALESTATE_SELECT_ALL = "SELECT ADDR_LN1,ADDR_LN2,AGENCY_FEES,ARCHIVE,AREA,ASPECT,AVAILABILITY,BALCONY,BATCH,BATHROOM,BED,BEDROOM,CATEGORY,CELLAR,CHARGE,CHARGE_DEPOSIT,CONDITIONS,COUNTY,COUNTRY,CREATEDATE,CUPBOARD,DEPARTURE,DEPARTURE_CUSTOMER,DEPOSIT,DESCRIPTION,ELEVATOR,FIREPLACE,FLOOR,FURNISHED,GARAGE,GARDEN,HALL,HEATING,ID,ID_CUSTOMER,BUNCHKEY,KITCHEN,LAST_UPDATE,LAST_USER,LIFEANNUITY,LIVINGROOM,LOGGIA,MEZZANINE,NUM_CONSULT_CC,NUM_CONSULT_WEB,OBSERVATION,ONLYOPTION,OPTION_FISC,PANEL,PARKING,PHOTO1,PHOTO2,PHOTO3,PHOTO4,PHOTO5,PHOTO6,PHOTO7,PHOTO8,PHOTO9,PHOTO10,PHOTOS_VALID,POOL,PLOT,PRINT_OBSERVATION,REF,REF_INTERNAL,RENT,RESERVE,RESTROOM,ROOFGARDEN,SALE,SECTOR,SHOWER,SORT,STANDING,STATE,STYLEFLOOR,SURFACE_FLOOR,SURFACE_GROUND,TV,TYPE,VACATION,WEB,ZIP,AGENCY_ID,EXIT_CUSTOMER,S_GARAGE,S_LIVINGROOM,S_HALL,S_ROOFGARDEN,S_MEZZANINE,S_LOGGIA,S_ROOM,S_CELLAR,CUSTOMER,RESIDANCE,S_KITCHEN,S_BALCONY,CELLAR_WINE,WALLS,DUPLEX,S_CELLAR_WINE FROM REALESTATE WHERE AGENCY_ID=?";
    public static final String REALESTATE_NEW_ID = "SELECT MAX(ID)+1 FROM REALESTATE";
    public static final String AVAILABILITY_INSERT = "INSERT INTO AVAILABILITY (ID,LAST_UPDATE) VALUES (?,?)";
    public static final String AVAILABILITY_UPDATE = "UPDATE AVAILABILITY SET ID=?,REALESTATE_ID=?,CUSTOMER_ID=?,BEGINDATE=?,ENDDATE=?,TAKEOPTION=?,CONFIRMED=?,LAST_UPDATE=?,LAST_USER=?,NOTE=? WHERE ID=? AND LAST_UPDATE=?";
    public static final String AVAILABILITY_DELETE = "DELETE FROM AVAILABILITY WHERE ID=? AND LAST_UPDATE=?";
    public static final String AVAILABILITY_SELECT = "SELECT ID,REALESTATE_ID,CUSTOMER_ID,BEGINDATE,ENDDATE,TAKEOPTION,CONFIRMED,LAST_UPDATE,LAST_USER,NOTE FROM AVAILABILITY WHERE ID=?";
    public static final String AVAILABILITY_SELECT_ALL = "SELECT ID,REALESTATE_ID,CUSTOMER_ID,BEGINDATE,ENDDATE,TAKEOPTION,CONFIRMED,LAST_UPDATE,LAST_USER,NOTE FROM AVAILABILITY WHERE REALESTATE_ID=?";
    public static final String AVAILABILITY_NEW_ID = "SELECT MAX(ID)+1 FROM AVAILABILITY";
    public static final String CUSTOMER_INSERT = "INSERT INTO CUSTOMER (ID,LASTTIME) VALUES (?,?)";
    public static final String CUSTOMER_UPDATE = "UPDATE CUSTOMER SET ID=?,STATUS=?,FIRSTNAME=?,LASTNAME=?,ADDRLN1=?,ADDRLN2=?,ZIP=?,CITY=?,STATE=?,COUNTRY=?,TEL=?,CEL=?,FAX=?,EMAIL=?,WWW=?,BANK=?,BANKNUMBER=?,PAYMENTBY=?,CARDNAME=?,CARDNUMBER=?,CARDEXPIRYDATE=?,CARDDIGICODE=?,NOTES=?,LASTUSERID=?,LASTTIME=?,SALESPERSONID=? WHERE ID=? AND LASTTIME=?";
    public static final String CUSTOMER_DELETE = "DELETE FROM CUSTOMER WHERE ID=? AND LASTTIME=?";
    public static final String CUSTOMER_SELECT = "SELECT ID,STATUS,FIRSTNAME,LASTNAME,ADDRLN1,ADDRLN2,ZIP,CITY,STATE,COUNTRY,TEL,CEL,FAX,EMAIL,WWW,BANK,BANKNUMBER,PAYMENTBY,CARDNAME,CARDNUMBER,CARDEXPIRYDATE,CARDDIGICODE,NOTES,LASTUSERID,LASTTIME,SALESPERSONID FROM CUSTOMER WHERE ID=?";
    public static final String CUSTOMER_SELECT_ALL = "SELECT ID,STATUS,FIRSTNAME,LASTNAME,ADDRLN1,ADDRLN2,ZIP,CITY,STATE,COUNTRY,TEL,CEL,FAX,EMAIL,WWW,BANK,BANKNUMBER,PAYMENTBY,CARDNAME,CARDNUMBER,CARDEXPIRYDATE,CARDDIGICODE,NOTES,LASTUSERID,LASTTIME,SALESPERSONID FROM CUSTOMER WHERE SALESPERSONID=?";
    public static final String CUSTOMER_NEW_ID = "SELECT MAX(ID)+1 FROM CUSTOMER";
    public static final String SALES_PERSON_INSERT = "INSERT INTO SALESPERSON (ID,LASTTIME) VALUES (?,?)";
    public static final String SALES_PERSON_UPDATE = "UPDATE SALESPERSON SET ID=?,LASTNAME=?,FIRSTNAME=?,AREA=?,TEL=?,CEL=?,FAX=?,EMAIL=?,LEVEL=?,SALESRATE=?,TOTALSALESRATE=?,RESULTS=?,RESULTSDATE=?,OBJECTIF=?,OBJECTIFDATE=?,NOTES=?,LASTUSERID=?,LASTTIME=?,SALESPERSONFAMILY=?,ADDRLN1=?,ADDRLN2=?,ZIP=?,CITY=?,COUNTRY=? WHERE ID=? AND LASTTIME=?";
    public static final String SALES_PERSON_DELETE = "DELETE FROM SALESPERSON WHERE ID=? AND LASTTIME=?";
    public static final String SALES_PERSON_SELECT = "SELECT ID,LASTNAME,FIRSTNAME,AREA,TEL,CEL,FAX,EMAIL,LEVEL,SALESRATE,TOTALSALESRATE,RESULTS,RESULTSDATE,OBJECTIF,OBJECTIFDATE,NOTES,LASTUSERID,LASTTIME,SALESPERSONFAMILY,ADDRLN1,ADDRLN2,ZIP,CITY,COUNTRY FROM SALESPERSON WHERE ID=?";
    public static final String SALES_PERSON_SELECT_ALL = "SELECT ID,LASTNAME,FIRSTNAME,AREA,TEL,CEL,FAX,EMAIL,LEVEL,SALESRATE,TOTALSALESRATE,RESULTS,RESULTSDATE,OBJECTIF,OBJECTIFDATE,NOTES,LASTUSERID,LASTTIME,SALESPERSONFAMILY,ADDRLN1,ADDRLN2,ZIP,CITY,COUNTRY FROM SALESPERSON ORDER BY LASTNAME";
    public static final String SALES_PERSON_NEW_ID = "SELECT MAX(ID)+1 FROM SALESPERSON";
}
